package com.lv.LvTourismForSale.dao;
import com.aqgj.util.BaseDaoImpl;
import com.aqgj.util.Pager;
import com.lv.LvTourismForSale.bean.LvTourismForSale;
import com.ztc.ZtcTransport.bean.ZtcTransport;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *
 * </p>
 *
 * @ClassName LvTourismForSaleDao
 * @JDK 1.5
 * @author
 * @date
 */
@Repository("LvTourismForSaleDao")
public class LvTourismForSaleDao extends BaseDaoImpl<LvTourismForSale> {
    /**
     * 修改
     * @param lvTourismForSale 实体bean
     * @return 0.失败，1.成功
     */
    public String update(LvTourismForSale lvTourismForSale) {
        this.getHibernateTemplate().saveOrUpdate(lvTourismForSale);
        if (lvTourismForSale.getId() != null) {
            return "1";
        }
        return "0";
    }

    /**
     * 搜索在售旅游列表
     * @param sql sql语句
     * @return
     */
    public List<LvTourismForSale> search(String sql) {
        List<LvTourismForSale> list = this.currentSession().createNativeQuery(sql,LvTourismForSale.class).getResultList();
        return list;
    }

    /**
     * 获取某一页的在线旅游列表数据
     * @param sql sql语句
     * @param pageNumber 第几页
     * @param pageSize 每页大小
     * @return
     */
    public List<LvTourismForSale> searchWithPage(String sql,String pageNumber,String pageSize) {
        int startIndex=0;
        int endIndex=Integer.parseInt(pageSize);
        if(pageNumber==null){
            pageNumber="0";
        }else{
            int number=Integer.parseInt(pageNumber);
            if(!"0".equals(pageNumber)){
                number=number-1;
            }
            startIndex=(number)*Integer.parseInt(pageSize);
        }
        sql+=" limit "+startIndex+","+endIndex;
        List<LvTourismForSale> list = this.currentSession().createNativeQuery(sql,LvTourismForSale.class).getResultList();
        return list;
    }

    /**
     * 删除
     * @param sql sql语句
     * @return
     */
    public String delete(String sql) {
        if (this.currentSession().createNativeQuery(sql).executeUpdate() >= 0) {
            return "1";
        }
        return "0";
    }

    /** 获取当前公司下的所有订单（直通车） */
    public Pager<Map> getList(ZtcTransport ztcTransport, Integer pageSize, Integer pageNumber, String orderId,String startDate,String endDate,String departDateStart,String departDateEnd,String contactName,String contactPhone,String orderStatus) {
        Pager<Map> pager=new Pager<>();
        pager.setPageSize(pageSize);
        pager.setCurrentPage(pageNumber);
        String sql="SELECT o.id,o.order_number, " +
                " o.start_site_name start_city, " +
                " o.terminal_site_name end_city, " +
                " o.tourism_for_sale_server_date, " +
                " o.order_time, " +
                " o.order_balance, " +
                " c.remain_seat_number, " +
                " c.remain_child_seat_number, " +
                " o.status,o.contact_name,o.contact_phone, " +
                "GROUP_CONCAT(CONCAT_WS('|',x.passenger_name, " +
                " case x.`status` " +
                " when 0 then '未退票' " +
                " when 1 then '已退票' " +
                " when 2 then '已过期' " +
                " end  " +
                "  ) SEPARATOR ';') pe, " +
                "IF(o.refund_balance is null,0,o.refund_balance) refund_balance " +
                "FROM lv_tourism_order o " ;
        if(departDateStart!=null&&!"".equals(departDateStart)&&departDateEnd!=null&&!"".equals(departDateEnd)){
            sql+=" and o.tourism_for_sale_server_date >='"+departDateStart+"' and o.tourism_for_sale_server_date<='"+departDateEnd+" 23:59:59' ";

        }
        sql+="INNER join lv_tourism_order_customer x on o.id=x.fk_tourism_order_id " +
                "LEFT JOIN " +
                " (" +
                "SELECT a.fk_order_id, " +
                " remain_seat_number, IFNULL(remain_child_seat_number, " +
                " 0) remain_child_seat_number " +
                "FROM " +
                " (" +
                "SELECT t.fk_tourism_order_id, COUNT(t.id) remain_seat_number " +
                "FROM lv_tourism_order_customer t " +
                "WHERE t.passenger_type = 0 " +
                "GROUP BY t.fk_tourism_order_id) a " +
                "LEFT JOIN ( " +
                "SELECT t.fk_tourism_order_id, COUNT(t.id) remain_child_seat_number " +
                "FROM lv_tourism_order_customer t " +
                "WHERE t.passenger_type = 1 " +
                "GROUP BY t.fk_tourism_order_id) b ON a.fk_tourism_order_id = b.fk_tourism_order_id) c ON o.id=c.fk_tourism_order_id " +
                "WHERE 1=1 ";
        if (orderStatus != null && !"".equals(orderStatus)&&!"全部".equals(orderStatus.trim())) {
            switch (orderStatus){
                case "已完成":orderStatus="3";break;
                case "待出行":orderStatus="2";break;
                case "出票中":orderStatus="1";break;
                case "已退款":orderStatus="5";break;
            }
            sql += " and o.status = "+orderStatus;
        } else {
            sql += " and o.status > 0 ";
        }
        if (ztcTransport.getType() != 0) {
            sql += " and o.fk_transport_id='" + ztcTransport.getId() + "'";
        }
        if (orderId != null && !"".equals(orderId.trim())) {
            sql += " and o.order_number like '%" + orderId + "%'";
        }
        if(contactName!=null&&!"".equals(contactName.trim())){
            sql+=" and o.contact_name  like '%"+contactName+"%'";
        }
        if(contactPhone!=null&&!"".equals(contactPhone.trim())){
            sql+=" and o.contact_phone like '%"+contactPhone+"%'";
        }
        if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
            sql+=" and o.order_time >='"+startDate+"' and o.order_time<='"+endDate+" 23:59:59'";
        }
        sql+=" group by o.id order by order_time desc";
        pager.setContent((List<Map>) this.currentSession().createNativeQuery(sql).setFirstResult(pager.getStart()).setMaxResults(pageSize).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList());
        pager.setRecordTotal(this.currentSession().createNativeQuery(sql).getResultList().size());
        return pager;
    }
    /** 获取当前公司下的所有订单（直通车） */
    public List<Map> getList(ZtcTransport ztcTransport,String orderId,String startDate,String endDate,String departDateStart,String departDateEnd,String contactName,String contactPhone,String orderStatus) {
        String sql="SELECT o.id,o.order_number, " +
                " o.start_site_name start_city, " +
                " o.terminal_site_name end_city, " +
                " o.tourism_for_sale_server_date, " +
                " o.order_time, " +
                " o.order_balance, " +
                " c.remain_seat_number, " +
                " c.remain_child_seat_number, " +
                " o.status,o.contact_name,o.contact_phone, " +
                "GROUP_CONCAT(CONCAT_WS('|',x.passenger_name, " +
                " case x.`status` " +
                " when 0 then '未退票' " +
                " when 1 then '已退票' " +
                " when 2 then '已过期' " +
                " end  " +
                "  ) SEPARATOR ';') pe, " +
                "IF(o.refund_balance is null,0,o.refund_balance) refund_balance " +
                "FROM lv_tourism_order o " ;
        if(departDateStart!=null&&!"".equals(departDateStart)&&departDateEnd!=null&&!"".equals(departDateEnd)){
            sql+=" and o.tourism_for_sale_server_date >='"+departDateStart+"' and o.tourism_for_sale_server_date<='"+departDateEnd+" 23:59:59' ";

        }
        sql+="INNER join lv_tourism_order_customer x on o.id=x.fk_tourism_order_id " +
                "LEFT JOIN " +
                " (" +
                "SELECT a.fk_order_id, " +
                " remain_seat_number, IFNULL(remain_child_seat_number, " +
                " 0) remain_child_seat_number " +
                "FROM " +
                " (" +
                "SELECT t.fk_tourism_order_id, COUNT(t.id) remain_seat_number " +
                "FROM lv_tourism_order_customer t " +
                "WHERE t.passenger_type = 0 " +
                "GROUP BY t.fk_tourism_order_id) a " +
                "LEFT JOIN ( " +
                "SELECT t.fk_tourism_order_id, COUNT(t.id) remain_child_seat_number " +
                "FROM lv_tourism_order_customer t " +
                "WHERE t.passenger_type = 1 " +
                "GROUP BY t.fk_tourism_order_id) b ON a.fk_tourism_order_id = b.fk_tourism_order_id) c ON o.id=c.fk_tourism_order_id " +
                "WHERE 1=1 ";
        if (orderStatus != null && !"".equals(orderStatus)&&!"全部".equals(orderStatus.trim())) {
            switch (orderStatus){
                case "已完成":orderStatus="3";break;
                case "待出行":orderStatus="2";break;
                case "出票中":orderStatus="1";break;
                case "已退款":orderStatus="5";break;
            }
            sql += " and o.status = "+orderStatus;
        } else {
            sql += " and o.status > 0 ";
        }
        if (ztcTransport.getType() != 0) {
            sql += " and o.fk_transport_id='" + ztcTransport.getId() + "'";
        }
        if (orderId != null && !"".equals(orderId.trim())) {
            sql += " and o.order_number like '%" + orderId + "%'";
        }
        if(contactName!=null&&!"".equals(contactName.trim())){
            sql+=" and o.contact_name  like '%"+contactName+"%'";
        }
        if(contactPhone!=null&&!"".equals(contactPhone.trim())){
            sql+=" and o.contact_phone like '%"+contactPhone+"%'";
        }
        if(startDate!=null&&!"".equals(startDate)&&endDate!=null&&!"".equals(endDate)){
            sql+=" and o.order_time >='"+startDate+"' and o.order_time<='"+endDate+" 23:59:59'";
        }
        sql+=" group by o.id order by order_time desc";
        return this.currentSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
    }
}